﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;

namespace OtechWebServer.CommClass
{
    public class ExcelClass
    {
        public Application ExcelApp { get; set; }
        public Worksheet ExcelWksheet { get; set; }
        public string FilePath { get; set; }

        public ExcelClass()
        {
            ExcelApp = new Application();
            ExcelApp.Visible = false;
            ExcelApp.UserControl = true;
            ExcelApp.DisplayAlerts = false;
            Workbooks wbooks = ExcelApp.Workbooks;
            
            object miss = Missing.Value;
            Workbook wbk = wbooks.Add(miss);
            Sheets sheets = wbk.Worksheets;
            
            ExcelWksheet = sheets.get_Item(1);            
        }

        public ExcelClass(string path)
        {
            ExcelApp = new Application();
            ExcelApp.Visible = false;
            ExcelApp.UserControl = true;
            ExcelApp.DisplayAlerts = false;
            Workbooks wbooks = ExcelApp.Workbooks;
            FilePath = path;
            Workbook wbk = wbooks.Add(path);
            Sheets sheets = wbk.Worksheets;

            ExcelWksheet = sheets.get_Item(1);
        }


        public void Save()
        {
            if (File.Exists(FilePath))
            {
                File.Delete(FilePath);
            }
            ExcelWksheet.SaveAs(FilePath);
            ExcelApp.DisplayAlerts = true;
            ExcelApp.Workbooks.Close();
            ExcelApp.Quit();
            GC.Collect();
        }

        public void Save(string filepath)
        {
            if (File.Exists(filepath))
            {
                File.Delete(filepath);
            }
            ExcelWksheet.SaveAs(filepath);            
            ExcelApp.DisplayAlerts = true;
            ExcelApp.Workbooks.Close();
            ExcelApp.Quit();
            GC.Collect();
        }
          
        /// <summary>
        /// 从指定的位置开始添加数据
        /// </summary>
        /// <param name="strBeginCell">开始位置</param>
        /// <param name="dt">要添加的数据</param>
        public void AddDataWithIndex(string strBeginCell,
            System.Data.DataTable dt)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ExcelWksheet.Range[strBeginCell].Offset[i, j].Value =
                        dt.Rows[i][j].ToString();
                }
            }
        }

        /// <summary>
        /// 从表中已有的数据的下一行开始添加数据  如果本身无数据 则加入列名
        /// </summary>
        /// <param name="dt"></param>
        public void AppendData(System.Data.DataTable dt)
        {

            int count = ExcelWksheet.UsedRange.Rows.Count;
            string startCell = "A" + (count + 1);
            //无数据时，将列名加入第一行
            if (count==1)
            {               
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ExcelWksheet.Range["A1"].Offset[0, i].Value =
                        dt.Columns[i].ColumnName;
                }
                startCell = "A2";
            }
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ExcelWksheet.Range[startCell].Offset[i, j].Value =
                        dt.Rows[i][j].ToString();
                }
            }
        }


        //public static DataSet LoadDataFromExcel()
        //{
        //    try
        //    {
        //        string strConn;
        //        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        //            //HttpContext.Current.Server.MapPath(filePath) + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
        //             filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
        //        OleDbConnection OleConn = new OleDbConnection(strConn);
        //        OleConn.Open();
        //        String sql = "SELECT * FROM [Sheet$]";//可是更改Sheet名称，比如sheet2，等等

        //        OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
        //        DataSet OleDsExcle = new DataSet();
        //        OleDaExcel.Fill(OleDsExcle, "Sheet1");
        //        OleConn.Close();
        //        return OleDsExcle;
        //    }
        //    catch (Exception err)
        //    {

        //        return null;
        //    }
        //}

    }
}